﻿using NPOI.SS.UserModel;
using System;
using System.Linq;

namespace Soul.XLS
{
    public static class IWorksheetExtensions
    {
        public static void SetRow(this ISheet sheet, int row, Action<IRow> configure)
        {
            var excelRow = sheet.GetOrAddRow(row);
            configure(excelRow);
        }
       
        public static void SetCell(this IRow row, int column, Action<ICell, ExcelCellStyle> configure)
        {
            var cell = row.GeOrAddCell(column); 
            var cellStyle = row.Sheet.Workbook.CreeteExcelCellStyle();
            configure(cell, cellStyle);
            cell.CellStyle = cellStyle.Style;
        }

        public static void SetCell(this ISheet sheet, int row, int column, Action<ICell, ExcelCellStyle> configure)
        {
            var cell = sheet.GeOrAddCell(row, column);
            var cellStyle = sheet.Workbook.CreeteExcelCellStyle();
            configure(cell, cellStyle);
            cell.CellStyle = cellStyle.Style;
        }

        public static IRow GetOrAddRow(this ISheet sheet, int row)
        {
            var sheetRow = sheet.GetRow(row);
            if (sheetRow == null)
            {
                sheetRow = sheet.CreateRow(row);
            }
            return sheetRow;
        }

        public static ICell GeOrAddCell(this IRow row, int column)
        {
            var cell = row.GetCell(column);
            if (cell == null)
                cell = row.CreateCell(column);
            return cell;    

        }

        public static ICell GeOrAddCell(this ISheet sheet, int row, int column)
        {
            return sheet.GetOrAddRow(row).GeOrAddCell(column);
        }

        public static void AddMergedRegion(this ISheet sheet, int startRow, int endRow, int startColumn, int endColumn)
        {
            if (!(endRow - startRow == 0 && endColumn - startColumn == 0))
            {
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startRow, endRow, startColumn, endColumn));
            }
        }

        public static void SetData(this ICell cell, object value)
        {
            if (value == null)
            {
                cell.SetBlank();
            }
            else if (value is DateTime)
            {
                cell.SetCellValue((DateTime)value);
            }
            else if (value is ValueType)
            {
                cell.SetCellValue(Convert.ToDouble(value));
            }
            else if (value is ExcelFormula formula)
            {
                cell.SetCellFormula(formula.Value);
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }

        public static object GetData(this ICell cell)
        {
            if (cell.CellType == CellType.Numeric)
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return cell.DateCellValue;
                }
                return cell.NumericCellValue;
            }
            else if (cell.CellType == CellType.Formula)
            {
                if (cell.CachedFormulaResultType == CellType.String)
                {
                    return cell.StringCellValue;
                }
                return cell.NumericCellValue;
            }
            else if (cell.CellType == CellType.Boolean)
            {
                return cell.BooleanCellValue;
            }
            else if (cell.CellType == CellType.String)
            {
                return cell.StringCellValue;
            }
            else
            {
                return null;
            }
        }

        private static void SetRangeCellStyle(this ISheet sheet, ICellStyle style, int startRow, int endRow, int startColumn, int endColumn)
        {
            for (int row = startRow; row <= endRow; row++)
            {
                for (int column = startColumn; column <= endColumn; column++)
                {
                    var cell = sheet.GeOrAddCell(row, column);
                    cell.CellStyle = style;
                }
            }
        }

        public static void InsertTable(this ISheet sheet, ExcelTable table, int firstRow = 0, int firstColumn = 0)
        {
            var workbook = sheet.Workbook;
            var rowIndex = firstRow;
            if (table.Headers != null && !string.IsNullOrEmpty(table.Headers.Title))
            {
                var row = sheet.GetOrAddRow(rowIndex);
                if (table.Headers.TitleRowHeilght.HasValue)
                {
                    row.Height = (short)(table.Headers.TitleRowHeilght.Value * 100);
                }
                var cell = row.GeOrAddCell(firstColumn);
                cell.SetData(table.Headers.Title);
                var startRow = firstRow;
                var endRow = firstRow;
                var startColumn = firstColumn;
                var endColumn = firstColumn + table.Columns.Count - 1;
                sheet.AddMergedRegion(startRow, endRow, startColumn, endColumn);
                var style = workbook.CreeteExcelCellTitleStyle();
                table.Headers.ApplyTitleStyle(style);
                sheet.SetRangeCellStyle(style.Style, startRow, endRow, startColumn, endColumn);
                rowIndex++;
            }
            if (table.Headers != null && table.Headers.Any())
            {
                var root = new ExcelColumn(table.Headers);
                root.CalcSpan();
                var depth = root.GetDepth();
                InsertTableHeaders(sheet, root, rowIndex, firstColumn);
                rowIndex += depth - 1;
            }
            if (table.Columns != null && table.Columns.Any())
            {
                var columnIndex = firstColumn;
                foreach (var item in table.Columns)
                {
                    item.ApplyDataStyle(workbook);
                    sheet.SetColumnWidth(columnIndex, item.DataStyleValue.ColumnWidth * 380);
                    columnIndex++;
                }
            }
            foreach (var row in table.Rows)
            {
                var columnIndex = firstColumn;
                var excelRow = sheet.GetOrAddRow(rowIndex);
                foreach (var column in table.Columns)
                {
                    var cell = excelRow.GeOrAddCell(columnIndex);
                    cell.CellStyle = column.DataStyleValue.Style;
                    cell.SetData(row[column.Code]);
                    columnIndex++;
                }
                if (row.Height != null)
                {
                    excelRow.Height = (short)row.Height.Value;
                }
                rowIndex++;
            }
            foreach (var footer in table.Footers)
            {
                var columnIndex = firstColumn;
                var excelRow = sheet.GetOrAddRow(rowIndex);
                foreach (var column in table.Columns)
                {
                    var cell = excelRow.GeOrAddCell(columnIndex);
                    var footerItem = footer.GetCell(column.Code);
                    if (footerItem == null)
                    {
                        continue;
                    }
                    cell.SetData(footerItem.Value);
                    var style = workbook.CreeteExcelCellStyle();
                    style.Alignment = HorizontalAlignment.Right;
                    footerItem.Style?.Invoke(style);
                    cell.CellStyle = style.Style;
                    columnIndex++;
                }
                if (footer.Height != null)
                {
                    excelRow.Height = (short)footer.Height.Value;
                }
                rowIndex++;
            }
            foreach (var item in table.MergeRegions)
            {
                var startRow = firstRow + item.FirstRow;
                var endRow = firstRow + item.LastRow;
                var startColumn = firstColumn + item.FirstColumn;
                var endColumn = firstColumn + item.LastColumn;
                sheet.AddMergedRegion(startRow, endRow, startColumn, endColumn);
            }
            if (table.FreezeRegion != null)
            {
                sheet.CreateFreezePane(table.FreezeRegion.Column, table.FreezeRegion.Row);
            }
            foreach (var item in table.GroupRows)
            {
                sheet.GroupRow(item.Row, item.Column);
            }
        }

        private static void InsertTableHeaders(ISheet sheet, ExcelColumn header, int rowIndex, int columnIndex = 0)
        {
            if (!header.Items.Any() || header.Items == null)
            {
                return;
            }

            foreach (var item in header.Items)
            {
                var startRow = rowIndex;
                var endRow = startRow + item.RowSpan.Value - 1;
                var startColumn = columnIndex;
                var endColumn = columnIndex + item.ColSpan.Value - 1;
                var cell = sheet.GeOrAddCell(startRow, startColumn);
                cell.SetData(item.Name);
                var style = header.ApplyColumnStyle(sheet.Workbook.CreeteExcelCellStyle());
                sheet.SetRangeCellStyle(style.Style, startRow, endRow, startColumn, endColumn);
                sheet.AddMergedRegion(startRow, endRow, startColumn, endColumn);
                InsertTableHeaders(sheet, item, rowIndex + 1, columnIndex);
                columnIndex = endColumn + 1;
            }
        }

        public static ExcelTable ExportTable(this ISheet sheet, int firstRow = 0, int firstColumn = 0, int? ColumnCount = null)
        {
            var row = GetOrAddRow(sheet, firstRow);
            if (ColumnCount == null)
            {
                ColumnCount = row.Cells.Count - firstColumn;
            }
            var columns = new ExcelColumnCollection();
            for (int columnIndex = firstColumn; columnIndex < ColumnCount; columnIndex++)
            {
                var cell = row.GeOrAddCell(columnIndex);
                columns.Add(new ExcelColumn(cell.GetData().ToString()));
            }
            return sheet.ExportTable(columns, firstRow + 1, firstColumn);
        }

        public static ExcelTable ExportTable(this ISheet sheet, ExcelColumnCollection columns, int firstRow = 0, int firstColumn = 0)
        {
            var table = new ExcelTable(sheet.SheetName);
            table.ApplyHeaders(columns);
            for (int columnIndex = firstColumn; columnIndex < columns.Count; columnIndex++)
            {
                columns[columnIndex - firstColumn].ColumnIndex = columnIndex;
            }
            for (int rowIndex = firstRow; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
            {
                var row = sheet.GetOrAddRow(rowIndex);
                var cells = row.Cells.Where(a => a.ColumnIndex >= firstColumn);
                if (!cells.Any() || cells.All(s => s.GetData() == null))
                {
                    continue;
                }
                var dataRow = table.NewRow();
                for (int i = firstColumn; i < firstColumn + columns.Count; i++)
                {
                    foreach (var cell in row.Cells.Where(a => a.ColumnIndex >= i))
                    {
                        var column = columns.Where(a => a.ColumnIndex == cell.ColumnIndex).FirstOrDefault();
                        if (column == null)
                        {
                            continue;
                        }
                        dataRow[column.Name] = cell.GetData();
                    }
                }
                table.Rows.Add(dataRow);
            }
            return table;
        }
    }
}
